Optimization of Dynamically Generated Sql Queries for Tiny-huge, Huge-tiny Problem

In most new commercial business software applications like Customer Relationship Management, the data is stored in the database layer which is usually a Relational Database Management System (RDBMS) like Oracle, DB2 UDB or SQL Server. To access data from these databases, Structured Query Language (SQL) queries are used that are generated dynamically at run time based on defined business models and business rules. One such business rule is visibilitythe capability of the application to restrict data access based on the role and responsibility of the user logged in to the application. This is generally achieved by appending security predicates in the form of sub-queries to the main query based on the roles and responsibility of the user. In some cases, the outer query may be more restrictive while in other cases, the security predicates may be more restrictive. This often results in a dilemma for the cost-based optimizer (CBO) of the backend database whether to drive from the outer query or drive from the security predicate sub-queries. This dilemma is sometimes called the “Tiny-Huge, Huge-Tiny” problem and results in serious performance degradation by way of increased response times on the application User Interface (UI). This paper provides a case study of a new approach to vastly reduce this CBO dilemma by a combination of denormalized columns and re-writing of the security predicates’ sub-queries at run-time, thereby levelling the outer and security sub-queries. This approach results in more stable execution plans in the database and much better performance of such SQLs, effectively leading to higher performance and scalability of the application.